﻿using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using Monti.MVVM;
using System.Windows.Input;

namespace SqliteHelper.Classes
{
   /// <summary>
   /// View model for copying a table.
   /// </summary>
   public class CopyTableViewModel : DialogBase
   {
      private int m_FieldToIndex;
      private int m_FieldFromIndex;
      private string m_WhereClause;
      private SqliteDatabase m_DatabaseTo;
      private SqliteDatabase m_DatabaseFrom;
      private FieldMapping m_SelectedMapping;
      private TreeViewItemViewModel m_TableTo;
      private TreeViewItemViewModel m_TableFrom;
      private TreeViewItemViewModel m_FieldTo;
      private TreeViewItemViewModel m_FieldFrom;
      private RelayCommand m_CmdAddMapping;
      private RelayCommand m_CmdDeleteMapping;

      /// <summary>
      /// Initializes a new copy table view model.
      /// </summary>
      /// <param name="mainViewModel"></param>
      public CopyTableViewModel (MainViewModel mainViewModel)
      {
         this.Width = 400;
         this.Height = 450;
         this.OkButtonText = "Copy";
         this.CancelButtonText = "Close";
         this.Parent = mainViewModel;
         this.FieldMappings = new ObservableCollection <FieldMapping> ();
      }

      /// <summary>
      /// Gets the parent view model.
      /// </summary>
      public MainViewModel Parent { get; private set; }

      /// <summary>
      /// Gets or Sets the database we are copying from.
      /// </summary>
      public SqliteDatabase DatabaseFrom
      {
         get { return this.m_DatabaseFrom; }
         set
         {
            this.m_DatabaseFrom = value;
            this.FieldMappings.Clear ();
            this.RaisePropertyChanged ("DatabaseFrom");
         }
      }

      /// <summary>
      /// Gets or Sets the database we are copying to.
      /// </summary>
      public SqliteDatabase DatabaseTo
      {
         get { return this.m_DatabaseTo; }
         set
         {
            this.m_DatabaseTo = value;
            this.FieldMappings.Clear ();
            this.RaisePropertyChanged ("DatabaseTo");
         }
      }

      /// <summary>
      /// Gets or Sets the table we are copying from.
      /// </summary>
      public TreeViewItemViewModel TableFrom
      {
         get { return this.m_TableFrom; }
         set
         {
            this.m_TableFrom = value;
            this.FieldMappings.Clear ();
            this.RaisePropertyChanged ("TableFrom");
         }
      }

      /// <summary>
      /// Gets or Sets the table we are copying to.
      /// </summary>
      public TreeViewItemViewModel TableTo
      {
         get { return this.m_TableTo; }
         set
         {
            this.m_TableTo = value;
            this.FieldMappings.Clear ();
            this.RaisePropertyChanged ("TableTo");
         }
      }

      /// <summary>
      /// Gets or Sets the field index we are copying from.
      /// </summary>
      public int FieldFromIndex
      {
         get { return this.m_FieldFromIndex; }
         set
         {
            this.m_FieldFromIndex = value;
            this.RaisePropertyChanged ("FieldFromIndex");
         }
      }

      /// <summary>
      /// Gets or Sets the field we are copying from.
      /// </summary>
      public TreeViewItemViewModel FieldFrom
      {
         get { return this.m_FieldFrom; }
         set
         {
            this.m_FieldFrom = value;
            this.RaisePropertyChanged ("FieldFrom");
         }
      }

      /// <summary>
      /// Gets or Sets the field index we are copying to.
      /// </summary>
      public int FieldToIndex
      {
         get { return this.m_FieldToIndex; }
         set
         {
            this.m_FieldToIndex = value;
            this.RaisePropertyChanged ("FieldToIndex");
         }
      }

      /// <summary>
      /// Gets or Sets the field we are copying to.
      /// </summary>
      public TreeViewItemViewModel FieldTo
      {
         get { return this.m_FieldTo; }
         set
         {
            this.m_FieldTo = value;
            this.RaisePropertyChanged ("FieldTo");
         }
      }

      /// <summary>
      /// Gets or Sets the where clause.
      /// </summary>
      public string WhereClause
      {
         get { return this.m_WhereClause; }
         set
         {
            this.m_WhereClause = value;
            this.RaisePropertyChanged ("WhereClause");
         }
      }

      /// <summary>
      /// Gets or Sets the selected field mapping.
      /// </summary>
      public FieldMapping SelecteMapping
      {
         get { return this.m_SelectedMapping; }
         set
         {
            this.m_SelectedMapping = value;
            this.RaisePropertyChanged ("SelecteMapping");
         }
      }

      /// <summary>
      /// Gets the list of field mappings.
      /// </summary>
      public ObservableCollection<FieldMapping> FieldMappings { get; private set; }

      /// <summary>
      /// Gets the add mapping command.
      /// </summary>
      public ICommand AddMappingCommand
      {
         get { return this.m_CmdAddMapping ?? (this.m_CmdAddMapping = new RelayCommand (AddMapping, CanAddMapping)); }
      }

      /// <summary>
      /// Gets the delete mapping command.
      /// </summary>
      public ICommand DeleteMappingCommand
      {
         get { return this.m_CmdDeleteMapping ?? (this.m_CmdDeleteMapping = new RelayCommand (DeleteMapping, CanDeleteMapping)); }
      }

      /// <summary>
      /// Checks if the add mapping command can execute.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      /// <returns>If the command can execute or not.</returns>
      private bool CanAddMapping (object obj)
      {
         if (this.FieldFrom != null && this.FieldTo != null)
         {
            if (this.FieldFrom.Tag.ToString () == this.FieldTo.Tag.ToString ())
            {
               var testMapping = this.FieldMappings.FirstOrDefault (mapping => mapping.To == this.FieldTo.Text);
               if (testMapping == null)
                  return true;
            }
         }
         return false;
      }

      /// <summary>
      /// Execution of the add mapping command.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      private void AddMapping (object obj)
      {
         this.FieldMappings.Add (new FieldMapping {From = this.FieldFrom.Text, To = this.FieldTo.Text});
         if (this.FieldFromIndex < this.TableFrom.Children.Count - 1)
            this.FieldFromIndex++;
         if (this.FieldToIndex < this.TableTo.Children.Count - 1)
            this.FieldToIndex++;
      }

      /// <summary>
      /// Checks if the delete mapping command can execute.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      /// <returns>If the command can execute or not.</returns>
      private bool CanDeleteMapping (object obj)
      {
         return this.SelecteMapping != null;
      }

      /// <summary>
      /// Execution of the delete mapping command.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      private void DeleteMapping (object obj)
      {
         this.FieldMappings.Remove (this.SelecteMapping);
      }

      /// <summary>
      /// Occurs when the ok button on the dialog is pressed.
      /// </summary>
      public override void OkExecuted ()
      {
         this.OkToClose = false;
         int numFields = this.FieldMappings.Count;
         string query = string.Format ("SELECT {0} FROM {1}{2};", this.SelectedFields, this.TableFrom.Text, 
            string.IsNullOrEmpty (this.WhereClause) ? string.Empty : " WHERE " + this.WhereClause);
         var data = this.DatabaseFrom.GetData (query);
         query = string.Format ("INSERT INTO {0}({1}) VALUES(", this.TableTo.Text, this.InsertFields);
         foreach (DataRow row in data.Rows)
         {
            string values = string.Empty;
            for (int a = 0; a < numFields; a++)
            {
               if (a != 0)
                  values += ", ";
               values += string.Format ("'{0}'", row[a].ToString ().Replace ("'", "''"));
            }
            string insertQuery = string.Format ("{0}{1});", query, values);
            this.DatabaseTo.ExecuteSql (insertQuery);
         }
         this.DatabaseTo.RefreshData ();
         this.FieldMappings.Clear ();
      }

      /// <summary>
      /// Gets a valid sqlite table name for the the given name.
      /// </summary>
      /// <param name="name">Name to convert to valid sqlite table name.</param>
      /// <returns>Valid name.</returns>
      public static string GetTableName (string name)
      {
         string tempBook = name;
         string number = string.Empty;
         while (char.IsDigit (tempBook[0]))
         {
            number += tempBook[0];
            tempBook = tempBook.Substring (1);
         }
         string tableName = tempBook.Trim () + number;
         tableName = tableName.Replace (" ", "_");

         return tableName;
      }

      /// <summary>
      /// Gets the list of fields to select.
      /// </summary>
      private string SelectedFields
      {
         get
         {
            string fieldList = string.Empty;
            for (int a = 0; a < this.FieldMappings.Count; a++)
            {
               if (a != 0)
                  fieldList += ", ";
               fieldList += this.FieldMappings [a].From;
            }
            return fieldList;
         }
      }

      /// <summary>
      /// Gets the list of fields to insert.
      /// </summary>
      private string InsertFields
      {
         get
         {
            string fieldList = string.Empty;
            for (int a = 0; a < this.FieldMappings.Count; a++)
            {
               if (a != 0)
                  fieldList += ", ";
               fieldList += this.FieldMappings[a].To;
            }
            return fieldList;
         }
      }
   }
}
